﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Transactions;
using System.Data.SqlClient;

using Global.Class.Library;
using DataTransferObject;
using DataTransferObject.MetaDataList;
using DataAccessLayer;
using DataAccessLayer.MetaDataList;
using DataAccessLayer.MetaDataList.ListCustomerCategoryDTSTableAdapters;

namespace BusinessLogicLayer.MetaDataList
{
    public class ListCustomerCategoryBLL : NotifyPropertyChangeObject
    {
        public GlobalEnum.TaskID TaskID { get { return GlobalEnum.TaskID.ListCustomerCategory; } }

        private UserInformation userOrganization;

        private ListCustomerCategoryMaster listCustomerTypeMaster;

        private BindingList<ListCustomerCategoryDetail> listCustomerTypeDetailList;



        public ListCustomerCategoryBLL()
        {
            try
            {
                if (GlobalVariables.shouldRestoreProcedure) RestoreProcedure();


                userOrganization = new UserInformation();

                listCustomerTypeMaster = new ListCustomerCategoryMaster();

                this.listCustomerTypeDetailList = new BindingList<ListCustomerCategoryDetail>();

                GlobalDefaultValue.Apply(this);


                this.ListCustomerCategoryMaster.PropertyChanged += new PropertyChangedEventHandler(ListCustomerCategoryMaster_PropertyChanged);

                this.ListCustomerCategoryDetailList.ListChanged += new ListChangedEventHandler(ListCustomerCategoryDetailList_ListChanged);

            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        private void ListCustomerCategoryMaster_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            this.SetDirty();
        }

        private void ListCustomerCategoryDetailList_ListChanged(object sender, ListChangedEventArgs e)
        {
            this.SetDirty();
        }




        #region <Adapter>

        private ListCustomerCategoryListingTableAdapter listingTableAdapter;
        protected ListCustomerCategoryListingTableAdapter ListingTableAdapter
        {
            get
            {
                if (listingTableAdapter == null) listingTableAdapter = new ListCustomerCategoryListingTableAdapter();
                return listingTableAdapter;
            }
        }

        private ListCustomerCategoryTableAdapter masterTableAdapter;
        protected ListCustomerCategoryTableAdapter MasterTableAdapter
        {
            get
            {
                if (masterTableAdapter == null) masterTableAdapter = new ListCustomerCategoryTableAdapter();
                return masterTableAdapter;
            }
        }

        private ListCustomerCategoryDetailTableAdapter detailTableAdapter;
        protected ListCustomerCategoryDetailTableAdapter DetailTableAdapter
        {
            get
            {
                if (detailTableAdapter == null) detailTableAdapter = new ListCustomerCategoryDetailTableAdapter();
                return detailTableAdapter;
            }
        }


        #endregion <Adapter>

        #region <Storage>

        public UserInformation UserOrganization
        {
            get { return this.userOrganization; }
            set { this.userOrganization = value; }
        }

        public ListCustomerCategoryDTS.ListCustomerCategoryListingDataTable ListCustomerCategoryListing()
        {
            return this.ListingTableAdapter.GetData();
        }

        public ListCustomerCategoryMaster ListCustomerCategoryMaster
        {
            get { return this.listCustomerTypeMaster; }
        }

        public BindingList<ListCustomerCategoryDetail> ListCustomerCategoryDetailList
        {
            get { return this.listCustomerTypeDetailList; }
        }


        #endregion <Storage>

        #region Properties

        #region <Primary Key>

        public int CustomerCategoryID   //Primary Key
        {
            get { return this.ListCustomerCategoryMaster.CustomerCategoryID; }
            private set
            {
                if (this.ListCustomerCategoryMaster.CustomerCategoryID != value)
                {
                    this.StopTracking();

                    this.ListCustomerCategoryMaster.CustomerCategoryID = value;

                    this.ListCustomerCategoryGetMaster();
                    this.ListCustomerCategoryGetDetail();

                    this.StartTracking();
                    this.Reset();
                }

            }
        }

        #endregion <Primary Key>

        private void ListCustomerCategoryGetMaster()
        {
            if (this.CustomerCategoryID > 0)
            {
                ListCustomerCategoryDTS.ListCustomerCategoryDataTable masterDataTable = this.MasterTableAdapter.GetData(this.CustomerCategoryID);

                if (masterDataTable.Count > 0)
                {
                    this.ListCustomerCategoryMaster.StopTracking();

                    this.ListCustomerCategoryMaster.EntryDate = masterDataTable[0].EntryDate;

                    this.ListCustomerCategoryMaster.AncestorID = masterDataTable[0].AncestorID;
                    this.ListCustomerCategoryMaster.PriceCategoryID = masterDataTable[0].PriceCategoryID;

                    this.ListCustomerCategoryMaster.DiscountPercentAverage = masterDataTable[0].DiscountPercentAverage;
                    this.ListCustomerCategoryMaster.CommissionPercentAverage = masterDataTable[0].CommissionPercentAverage;
                    this.ListCustomerCategoryMaster.VATPercent = masterDataTable[0].VATPercent;
                    this.ListCustomerCategoryMaster.VNRate = masterDataTable[0].VNRate;

                    this.ListCustomerCategoryMaster.Description = masterDataTable[0].Description;
                    this.ListCustomerCategoryMaster.Other = masterDataTable[0].Other;

                    this.ListCustomerCategoryMaster.StartTracking();

                    this.ListCustomerCategoryMaster.Reset();

                    this.UserOrganization.UserID = masterDataTable[0].UserID;
                    this.UserOrganization.UserOrganizationID = masterDataTable[0].UserOrganizationID;
                }
                else throw new System.ArgumentException("Insufficient get data");
            }
            else
            {
                GlobalDefaultValue.Apply(this.ListCustomerCategoryMaster);
                this.ListCustomerCategoryMaster.EntryDate = DateTime.Today;
                this.ListCustomerCategoryMaster.Reset();
            }
        }


        private void ListCustomerCategoryGetDetail()
        {
            this.listCustomerTypeDetailList.RaiseListChangedEvents = false;
            this.listCustomerTypeDetailList.Clear();
            if (this.CustomerCategoryID >= 0)
            {

                ListCustomerCategoryDTS.ListCustomerCategoryDetailDataTable detailDataTable = this.DetailTableAdapter.GetData(this.CustomerCategoryID);

                if (detailDataTable.Count > 0)
                {
                    foreach (ListCustomerCategoryDTS.ListCustomerCategoryDetailRow detailRow in detailDataTable.Rows)
                    {
                        this.listCustomerTypeDetailList.Add(new ListCustomerCategoryDetail(detailRow.CommonID, detailRow.CommonValue, detailRow.Remarks));
                    }
                }
            }
            this.listCustomerTypeDetailList.RaiseListChangedEvents = true;
            this.listCustomerTypeDetailList.ResetBindings();
        }

        #endregion Properties





        #region Object Permission

        public override bool IsValid
        {
            get
            {
                List<ListCustomerCategoryDetail> inValidListCustomerCategoryDetail = this.ListCustomerCategoryDetailList.Where(listCustomerTypeDetail => !listCustomerTypeDetail.IsValid).ToList();
                return this.ListCustomerCategoryMaster.IsValid && inValidListCustomerCategoryDetail.Count == 0;
            }
        }

        public bool ReadOnly
        {
            get
            {
                try
                {
                    return GlobalUserPermission.GetUserReadOnly(GlobalVariables.GlobalUserInformation.UserID, this.TaskID);
                }
                catch
                {
                    return true;
                }
            }
        }

        public bool Editable
        {
            get
            {
                try
                {
                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, this.TaskID, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("ListCustomerCategoryEditable", this.CustomerCategoryID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Verifiable
        {
            get
            {
                try
                {
                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.ListCustomerCategoryVerifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("ListCustomerCategoryEditable", this.CustomerCategoryID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Unverifiable
        {
            get
            {
                try
                {
                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.ListCustomerCategoryUnverifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("ListCustomerCategoryEditable", this.CustomerCategoryID);
                }
                catch
                {
                    return false;
                }
            }
        }


        #endregion Object Permission






        public void Fill(int itemCategoryID)
        {
            if (this.CustomerCategoryID == itemCategoryID) this.CustomerCategoryID = -1; //Enforce to reload
            this.CustomerCategoryID = itemCategoryID;
        }

        public void New()
        {
            if (this.CustomerCategoryID == 0) this.CustomerCategoryID = -1;
            this.CustomerCategoryID = 0;
        }

        public void Edit()
        {

        }

        #region Save & Delete Method


        public bool Save()
        {
            int itemCategoryID = 0;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient save", "Save validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (this.ReadOnly) throw new System.ArgumentException("Insufficient save", "Uneditable");

                    if (!this.SaveMaster(ref itemCategoryID)) throw new System.ArgumentException("Insufficient save", "Save master");

                    if (!this.SaveDetail(itemCategoryID)) throw new System.ArgumentException("Insufficient save", "Save detail");

                    transactionScope.Complete();
                }

                SQLDatabase.ExecuteNonQuery("ListCustomerCategoryUpdateHierarchyLevel", CommandType.StoredProcedure, "AncestorID", this.listCustomerTypeMaster.AncestorID);

                this.Fill(itemCategoryID);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        public bool Delete()
        {
            if (this.listCustomerTypeMaster.CustomerCategoryID <= 0) return false;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient delete", "Delete validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient delete", "Uneditable");

                    if (!this.SaveUndo(this.listCustomerTypeMaster.CustomerCategoryID)) throw new System.ArgumentException("Insufficient delete", "Delete detail");

                    if (this.MasterTableAdapter.Delete(this.listCustomerTypeMaster.CustomerCategoryID) != 1) throw new System.ArgumentException("Insufficient delete", "Delete master");

                    transactionScope.Complete();
                }
                this.Fill(0);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        private bool SaveValidate()
        {
            ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

            this.UserOrganization = GlobalUserPermission.GetUserInformation(this.UserOrganization.UserID, this.ListCustomerCategoryMaster.EntryDate);

            if (this.UserOrganization.UserID <= 0 || this.UserOrganization.UserOrganizationID <= 0) exceptionTable.AddException(new string[] { GlobalVariables.stringFieldRequired, "User information" });

            if (exceptionTable.Table.Rows.Count <= 0 && this.IsValid) return true; else throw new CustomException("Save validate", exceptionTable.Table);
        }


        private bool SaveMaster(ref int itemCategoryID)
        {
            ListCustomerCategoryDTS.ListCustomerCategoryDataTable masterDataTable;
            ListCustomerCategoryDTS.ListCustomerCategoryRow masterRow;

            if (this.listCustomerTypeMaster.CustomerCategoryID <= 0) //Add
            {
                masterDataTable = new ListCustomerCategoryDTS.ListCustomerCategoryDataTable();
                masterRow = masterDataTable.NewListCustomerCategoryRow();
            }
            else //Edit
            {
                if (!this.SaveUndo(listCustomerTypeMaster.CustomerCategoryID)) throw new System.ArgumentException("Insufficient save", "Save undo");
                masterDataTable = this.MasterTableAdapter.GetData(listCustomerTypeMaster.CustomerCategoryID);
                if (masterDataTable.Count > 0) masterRow = masterDataTable[0]; else throw new System.ArgumentException("Insufficient save", "Get for edit");
            }

            masterRow.EntryDate = this.listCustomerTypeMaster.EntryDate;

            masterRow.AncestorID = this.listCustomerTypeMaster.AncestorID;
            masterRow.HierarchyLevel = 0;
            masterRow.HierarchyOrder = "";
            masterRow.PriceCategoryID = this.listCustomerTypeMaster.PriceCategoryID;

            masterRow.DiscountPercentAverage = this.listCustomerTypeMaster.DiscountPercentAverage;
            masterRow.CommissionPercentAverage = this.listCustomerTypeMaster.CommissionPercentAverage;
            masterRow.VATPercent = this.listCustomerTypeMaster.VATPercent;
            masterRow.VNRate = this.listCustomerTypeMaster.VNRate;

            masterRow.Description = this.listCustomerTypeMaster.Description;
            masterRow.Other = this.listCustomerTypeMaster.Other;

            masterRow.UserID = this.UserOrganization.UserID;
            masterRow.UserOrganizationID = this.UserOrganization.UserOrganizationID;

            if (this.listCustomerTypeMaster.CustomerCategoryID <= 0) masterDataTable.AddListCustomerCategoryRow(masterRow);

            int rowsAffected = this.MasterTableAdapter.Update(masterRow);

            itemCategoryID = masterRow.CustomerCategoryID;

            return rowsAffected == 1;

        }


        private bool SaveDetail(int itemCategoryID)
        {
            int serialID = 0; int rowsAffected = 0;


            #region <Save collection>

            serialID = 0;

            ListCustomerCategoryDTS.ListCustomerCategoryDetailDataTable detailDataTable = new ListCustomerCategoryDTS.ListCustomerCategoryDetailDataTable();

            foreach (ListCustomerCategoryDetail listCustomerTypeDetail in this.ListCustomerCategoryDetailList)
            {
                ListCustomerCategoryDTS.ListCustomerCategoryDetailRow detailRow = detailDataTable.NewListCustomerCategoryDetailRow();

                detailRow.CustomerCategoryID = itemCategoryID;
                detailRow.SerialID = ++serialID;

                detailRow.CommonID = listCustomerTypeDetail.CommonID;
                detailRow.CommonValue = listCustomerTypeDetail.CommonValue;

                detailRow.Remarks = listCustomerTypeDetail.Remarks;

                detailDataTable.AddListCustomerCategoryDetailRow(detailRow);
            }

            rowsAffected = this.DetailTableAdapter.Update(detailDataTable);
            if (rowsAffected != this.listCustomerTypeDetailList.Count) throw new System.ArgumentException("Insufficient save", "Save detail");


            #endregion <Save collection>


            return true;
        }


        private bool SaveUndo(int itemCategoryID)
        {
            this.DetailTableAdapter.Delete(itemCategoryID);

            return true;
        }



        #endregion



        private void RestoreProcedure()
        {
            string queryString;
            string[] queryArray;



            queryString = "     " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      CustomerCategoryID, Description, AncestorID, Other, EntryDate " + "\r\n";
            queryString = queryString + "       FROM        ListCustomerCategory " + "\r\n";
            queryString = queryString + "       ORDER BY    Description " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerCategoryListing", queryString);



            queryString = "     @CustomerCategoryID Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      CustomerCategoryID, Description, AncestorID, HierarchyLevel, HierarchyOrder, PriceCategoryID, DiscountPercentAverage, CommissionPercentAverage, VATPercent, VNRate, Other, EntryDate, UserID, UserOrganizationID " + "\r\n";
            queryString = queryString + "       FROM        ListCustomerCategory " + "\r\n";
            queryString = queryString + "       WHERE       CustomerCategoryID = @CustomerCategoryID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerCategorySelect", queryString);


            queryString = "     @Description nvarchar(100),	@AncestorID int, @HierarchyLevel int,  @HierarchyOrder nvarchar(50), @PriceCategoryID int, @DiscountPercentAverage float, @CommissionPercentAverage float, @VATPercent float, @VNRate float, @Other nvarchar(100), @EntryDate datetime, @UserID int, @UserOrganizationID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO ListCustomerCategory (Description, AncestorID, HierarchyLevel, HierarchyOrder, PriceCategoryID, DiscountPercentAverage, CommissionPercentAverage, VATPercent, VNRate, Other, EntryDate, UserID, UserOrganizationID) VALUES (@Description, @AncestorID, @HierarchyLevel, @HierarchyOrder, @PriceCategoryID, @DiscountPercentAverage, @CommissionPercentAverage, @VATPercent, @VNRate, @Other, @EntryDate, @UserID, @UserOrganizationID) " + "\r\n";
            queryString = queryString + "       SELECT      CustomerCategoryID, Description, AncestorID, HierarchyLevel, HierarchyOrder, PriceCategoryID, DiscountPercentAverage, CommissionPercentAverage, VATPercent, VNRate, Other, EntryDate, UserID, UserOrganizationID FROM ListCustomerCategory WHERE CustomerCategoryID = SCOPE_IDENTITY() " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerCategoryInsert", queryString);


            queryString = "     @CustomerCategoryID int, @Description nvarchar(100), @AncestorID int, @HierarchyLevel int,  @HierarchyOrder nvarchar(50), @PriceCategoryID int, @DiscountPercentAverage float, @CommissionPercentAverage float, @VATPercent float, @VNRate float, @Other nvarchar(100), @EntryDate datetime, @UserID int, @UserOrganizationID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      ListCustomerCategory SET Description = @Description, AncestorID = @AncestorID, HierarchyLevel = @HierarchyLevel, HierarchyOrder = @HierarchyOrder, PriceCategoryID = @PriceCategoryID, DiscountPercentAverage = @DiscountPercentAverage, CommissionPercentAverage = @CommissionPercentAverage, VATPercent = @VATPercent, VNRate = @VNRate, Other = @Other, EntryDate = @EntryDate, UserID = @UserID, UserOrganizationID = @UserOrganizationID WHERE CustomerCategoryID = @CustomerCategoryID " + "\r\n";
            queryString = queryString + "       SELECT      CustomerCategoryID, Description, AncestorID, HierarchyLevel, HierarchyOrder, PriceCategoryID, DiscountPercentAverage, CommissionPercentAverage, VATPercent, VNRate, Other, EntryDate, UserID, UserOrganizationID FROM ListCustomerCategory WHERE CustomerCategoryID = @CustomerCategoryID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerCategoryUpdate", queryString);


            queryString = " @CustomerCategoryID int ";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM ListCustomerCategory WHERE CustomerCategoryID = @CustomerCategoryID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("ListCustomerCategoryDelete", queryString);






            queryString = " @CustomerCategoryID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      CustomerCategoryID, SerialID, CommonID, CommonValue, Remarks " + "\r\n";
            queryString = queryString + "       FROM        ListCustomerCategoryDetail " + "\r\n";
            queryString = queryString + "       WHERE       CustomerCategoryID = @CustomerCategoryID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerCategoryDetailSelect", queryString);


            queryString = " @CustomerCategoryID int, @SerialID int,	@CommonID int, @CommonValue float, @Remarks nvarchar(100) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO ListCustomerCategoryDetail (CustomerCategoryID, SerialID, CommonID, CommonValue, Remarks) VALUES (@CustomerCategoryID, @SerialID, @CommonID, @CommonValue, @Remarks) " + "\r\n";
            queryString = queryString + "       SELECT      CustomerCategoryID, SerialID, CommonID, CommonValue, Remarks FROM ListCustomerCategoryDetail WHERE (CustomerCategoryID = @CustomerCategoryID) AND (SerialID = @SerialID) " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerCategoryDetailInsert", queryString);



            queryString = " @CustomerCategoryID int, @SerialID int,	@CommonID int, @CommonValue float, @Remarks nvarchar(100) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      ListCustomerCategoryDetail SET CommonID = @CommonID, CommonValue = @CommonValue, Remarks = @Remarks WHERE CustomerCategoryID = @CustomerCategoryID AND SerialID = @SerialID " + "\r\n";
            queryString = queryString + "       SELECT      CustomerCategoryID, SerialID, CommonID, CommonValue, Remarks FROM ListCustomerCategoryDetail WHERE CustomerCategoryID = @CustomerCategoryID AND SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerCategoryDetailUpdate", queryString);



            queryString = " @CustomerCategoryID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM ListCustomerCategoryDetail WHERE CustomerCategoryID = @CustomerCategoryID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("ListCustomerCategoryDetailDelete", queryString);



            queryString = " @AncestorID Int ";
            queryString = queryString + " WITH ENCRYPTION ";
            queryString = queryString + " AS ";

            queryString = queryString + "   DECLARE @AncestorHierarchyOrder NVarchar(30), @AncestorHierarchyLevel Int  ";
            queryString = queryString + "   DECLARE @Cursor_Rows Int, @SerialNo Int ";

            queryString = queryString + "   SET @AncestorHierarchyOrder = N'' ";
            queryString = queryString + "   SET @AncestorHierarchyLevel = 0 ";
            queryString = queryString + "   SET @SerialNo = 0 ";
            queryString = queryString + "   SET @Cursor_Rows = 0 ";

            queryString = queryString + "   IF @AncestorID <> 0 ";
            queryString = queryString + "   BEGIN ";

            queryString = queryString + "       DECLARE Ancestor_Cursor CURSOR LOCAL FOR ";
            queryString = queryString + "       SELECT TOP 1 HierarchyOrder, HierarchyLevel FROM ListCustomerCategory ";
            queryString = queryString + "       WHERE CustomerCategoryID = @AncestorID ";

            queryString = queryString + "       OPEN Ancestor_Cursor ";

            queryString = queryString + "       FETCH NEXT FROM Ancestor_Cursor INTO @AncestorHierarchyOrder, @AncestorHierarchyLevel ";

            queryString = queryString + "       CLOSE Ancestor_Cursor ";
            queryString = queryString + "       DEALLOCATE Ancestor_Cursor ";


            queryString = queryString + "   END ";

            queryString = queryString + "   DECLARE @CustomerCategoryID Int ";

            queryString = queryString + "   DECLARE CustomerCategory_Cursor CURSOR LOCAL SCROLL FOR ";
            queryString = queryString + "   SELECT CustomerCategoryID FROM ListCustomerCategory ";
            queryString = queryString + "   WHERE AncestorID = @AncestorID ORDER BY Description ";
            queryString = queryString + "   FOR UPDATE ";

            queryString = queryString + "   OPEN CustomerCategory_Cursor ";

            queryString = queryString + "   FETCH LAST FROM CustomerCategory_Cursor INTO @CustomerCategoryID ";
            queryString = queryString + "   IF @@FETCH_STATUS = 0 ";
            queryString = queryString + "   BEGIN ";
            queryString = queryString + "       SET @Cursor_Rows = @@CURSOR_ROWS ";
            queryString = queryString + "       FETCH FIRST FROM CustomerCategory_Cursor INTO @CustomerCategoryID ";
            queryString = queryString + "   END ";

            queryString = queryString + "   WHILE @@FETCH_STATUS = 0";
            queryString = queryString + "   BEGIN";

            queryString = queryString + "       SET @SerialNo = @SerialNo + 1 ";

            queryString = queryString + "       UPDATE ListCustomerCategory SET HierarchyOrder = @AncestorHierarchyOrder +  REPLICATE('0', DATALENGTH(CONVERT (NVarchar(30), @Cursor_Rows)) - DATALENGTH(CONVERT (NVarchar(30), @SerialNo))) + CONVERT (NVarchar(30), @SerialNo), HierarchyLevel = @AncestorHierarchyLevel + 1 ";
            queryString = queryString + "       WHERE CURRENT OF CustomerCategory_Cursor ";

            queryString = queryString + "       EXECUTE ListCustomerCategoryUpdateHierarchyLevel @AncestorID = @CustomerCategoryID ";

            queryString = queryString + "       FETCH NEXT FROM CustomerCategory_Cursor INTO @CustomerCategoryID ";

            queryString = queryString + "   END ";

            queryString = queryString + "   CLOSE CustomerCategory_Cursor ";
            queryString = queryString + "   DEALLOCATE CustomerCategory_Cursor ";

            SQLDatabase.CreateStoredProcedure("ListCustomerCategoryUpdateHierarchyLevel", queryString);



           

            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[4];
            queryArray[0] = "   SELECT      TOP 1 CustomerCategoryID FROM ListCustomerCategory WHERE AncestorID = @FindIdentityID ";
            queryArray[1] = "   SELECT      TOP 1 CustomerCategoryID FROM ListCustomerName WHERE CustomerCategoryID = @FindIdentityID ";
            queryArray[2] = "   SELECT      TOP 1 CustomerCategoryID FROM QuotaDetail WHERE CustomerCategoryID = @FindIdentityID ";
            queryArray[3] = "   SELECT      TOP 1 CustomerCategoryID FROM InMarketMaster WHERE CustomerCategoryID = @FindIdentityID ";

            SQLDatabase.CreateProcedureToCheckExisting("ListCustomerCategoryEditable", queryArray);
        }
  

        
    }
}
